using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Microsoft.ConnectionEngine.Registers.Core.ServiceProvider.Common;
using Microsoft.ConnectionEngine.Registers.Core.ServiceProvider.Common.DataSets;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Configuration;

namespace Microsoft.ConnectionEngine.Registers.Core.ServiceProvider.DataAccess
{
    public class ServiceProvider
    {

        #region Get/Search Methods

        [CLSCompliant(false)]
        public static dsProviderList GetAllowedServiceProviders(string serviceproviderid)
        {
            dsProviderList ds = new dsProviderList();
            string SQL = "ServiceProviderSelectbyOriginatingMessageProviderID";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            ds.ProviderList.Load(db.ExecuteReader(SQL, serviceproviderid), LoadOption.OverwriteChanges);


            return ds;
        }

        [CLSCompliant(false)]
        public static dsInteractiveSession GetInteractiveSessionList(string serviceproviderid)
        {
            dsInteractiveSession ds = new dsInteractiveSession();
            string SQL = "ServiceProviderSelectInteractiveSessionbyOriginatingMessageProviderID";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            ds.InteractiveSession.Load(db.ExecuteReader(SQL, serviceproviderid), LoadOption.OverwriteChanges);

            return ds;
        }

        [CLSCompliant(false)]
        public static dsProvider GetServiceProviderByID(string serviceproviderid)
        {
            dsProvider ds = new dsProvider();

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());

            ds.Provider.Load(db.ExecuteReader("ServiceProviderSelectbyID", serviceproviderid), LoadOption.OverwriteChanges);


            return ds;
        }

        /// <summary>
        /// Returns a list of service providers based on search criteria
        /// </summary>
        /// <param name="displayname">Name search field</param>
        /// <param name="serviceprovidertypeid">Service Provider type search field</param>
        /// <param name="poolid">Pool search ID</param>
        /// <returns></returns>
        [CLSCompliant(false)]
        public static dsProvider ServiceProviderSearch(string displayname, string serviceprovidertypeid, string poolid, bool inpool)
        {
            dsProvider ds = new dsProvider();
            string sp;
            
            //Work Item ID: 144
            //Modified By: SachinJo
            //Using Length instead of empty("") string comparision.
            if (serviceprovidertypeid.Trim().Length == 0) serviceprovidertypeid = null;
            if (poolid.Trim().Length == 0) poolid = null;

            if (inpool)
            {
                sp = "ServiceProviderSearch";
            }
            else
            {
                sp = "ServiceProviderSearchByPoolIDNotInPool";
            }
            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            ds.Provider.Load(db.ExecuteReader(sp, displayname, serviceprovidertypeid, poolid), LoadOption.OverwriteChanges);

            return ds;
        }

        /// <summary>
        /// Returns true if a ServiceProvider row exists
        /// </summary>
        /// <returns>True if successful</returns>
        public static int ServiceProviderExists(string ServiceProviderDescription)
        {
            
            string SQL = "ServiceProviderExists";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            return Convert.ToInt32(db.ExecuteScalar(SQL, ServiceProviderDescription));
            
               
        }

        public static int ServiceProviderInSamePool(string sourceserviceproviderid, string destinationserviceproviderid)
        {
            int result = 0;
            string SQL = "ServiceProviderInSamePool";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            result = (int)db.ExecuteScalar(SQL, sourceserviceproviderid, destinationserviceproviderid);

            return result;
        }

        #endregion

        #region Lock/Unlock methods

        /// <summary>
        /// Locks a service provider
        /// </summary>
        /// <param name="serviceproviderid">The service provider to be checked out</param>
        /// <param name="userID">The provider that is locking the object</param>
        /// <returns>True if successful)</returns>
        public static bool ServiceProviderLock(string serviceproviderid, string userID)
        {
            bool result = true;
            string SQL = "ServiceProviderLock";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            if (db.ExecuteNonQuery(SQL, serviceproviderid, userID) <= 0)
                result = false;

            return result;
        }

        /// <summary>
        /// Unlocks a service provider
        /// </summary>
        /// <param name="serviceproviderid">The service provider to be checked in</param>
        /// <returns>True if successful)</returns>
        public static bool ServiceProviderUnlock(string serviceproviderID)
        {
            bool result = true;
            string SQL = "ServiceProviderUnlock";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            if (db.ExecuteNonQuery(SQL, serviceproviderID) <= 0)
                result = false;

            return result;
        }


        /// <summary>
        /// Checks in all ServiceProviders checked out by a provider
        /// </summary>
        /// <param name="userID">The provider that is checking in its pools</param>
        /// <returns>True if successful)</returns>
        public static bool ServiceProviderUnlockAll(string userID)
        {
            bool result = true;
            string SQL = "ServiceProviderUnLockAll";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            db.ExecuteNonQuery(SQL, userID);

            return result;
        }

        /// <summary>
        /// Tests if a service provider is checked out
        /// </summary>
        /// <param name="serviceproviderid">The service provider to be tested</param>
        /// <returns>True if checked out</returns>
        public static bool ServiceProviderIsLocked(string serviceproviderid)
        {
            bool result = false;
            string SQL = "ServiceProviderIsLocked";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            if (Convert.ToInt32(db.ExecuteScalar(SQL, serviceproviderid)) != 0)
                result = true;

            return result;
        }


        /// <summary>
        /// Tests if a service provider is checked out by a user
        /// </summary>
        /// <param name="serviceproviderid">The service provider that is to to be tested</param>
        /// <param name="userID">The provider that may have the object checked out</param>
        /// <returns>True if successful</returns>
        public static bool ServiceProviderIsLockedByProvider(string serviceproviderid, string userID)
        {
            bool result = false;
            string SQL = "ServiceProviderIsLockedByUser";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            if (Convert.ToInt32(db.ExecuteScalar(SQL, serviceproviderid, userID)) != 0)
                result = true;

            return result;
        }

        #endregion

        #region Delete methods

        /// <summary>
        /// Deletes a service provider
        /// </summary>
        /// <param name="serviceproviderid">The service provider to delete</param>
        /// <returns>True if successful</returns>
        public static bool ServiceProviderDelete(string serviceproviderid)
        {
            bool result = true;
            string SQL = "ServiceProviderDelete";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            if (db.ExecuteNonQuery(SQL, serviceproviderid) <= 0)
                result = false;

            return result;
        }


        /// <summary>
        /// Deletes all ServiceProviderPool records belonging to a specific ServiceProvider
        /// </summary>
        /// <param name="serviceproviderid">The ID of the parent ServiceProvider</param>
        /// <returns>True if successful</returns>
        public static void ServiceProviderPoolDeleteByServiceProviderID(string serviceproviderid)
        {
            //Work Item ID: 143
            //Modified By: SachinJo
            //Commented field "result" as it's never used.
            //bool result = true;

            string SQL = "ServiceProviderPoolDeleteByServiceProviderID";
            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            db.ExecuteNonQuery(SQL, serviceproviderid);           
        }


        #endregion

        #region  Update/Insert methods

        /// <summary>
        /// Inserts a new ServiceProvider
        /// </summary>
        /// <param name="displayname"></param>
        /// <param name="serviceprovidertypeid"></param>
        /// <param name="systemname"></param>
        /// <param name="publickey"></param>
        /// <param name="webserviceurl"></param>
        /// <param name="contactname"></param>
        /// <param name="contactaddress1"></param>
        /// <param name="contactaddress2"></param>
        /// <param name="contactcity"></param>
        /// <param name="contactprovince"></param>
        /// <param name="contactpostalcode"></param>
        /// <param name="contactcountry"></param>
        /// <param name="contactphone"></param>
        /// <param name="contactfax"></param>
        /// <param name="contactemail"></param>
        /// <param name="encryptiontype"></param>
        /// <returns>A new ID if successful, otherwise empty string</returns>
        public static string ServiceProviderInsert
        (
            string displayname,
            int serviceprovidertypeid,
            string systemname,
            string publickey,
            string webserviceurl,
            string contactname,
            string contactaddress1,
            string contactaddress2,
            string contactcity,
            string contactprovince,
            string contactpostalcode,
            string contactcountry,
            string contactphone,
            string contactfax,
            string contactemail,
            string encryptiontype
        )
        {
            string result = string.Empty;
            string SQL = "ServiceProviderInsert";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            result = db.ExecuteScalar(SQL, 
                displayname,
                serviceprovidertypeid,
                systemname,
                publickey,
                webserviceurl,
                contactname,
                contactaddress1,
                contactaddress2,
                contactcity,
                contactprovince,
                contactpostalcode,
                contactcountry,
                contactphone,
                contactfax,
                contactemail,
                encryptiontype).ToString();

            return result;
        }

        /// <summary>
        /// Updates an existing ServiceProvider
        /// </summary>
        /// <param name="serviceproviderid"></param>
        /// <param name="displayname"></param>
        /// <param name="serviceprovidertypeid"></param>
        /// <param name="systemname"></param>
        /// <param name="publickey"></param>
        /// <param name="webserviceurl"></param>
        /// <param name="contactname"></param>
        /// <param name="contactaddress1"></param>
        /// <param name="contactaddress2"></param>
        /// <param name="contactcity"></param>
        /// <param name="contactprovince"></param>
        /// <param name="contactpostalcode"></param>
        /// <param name="contactcountry"></param>
        /// <param name="contactphone"></param>
        /// <param name="contactfax"></param>
        /// <param name="contactemail"></param>
        /// <param name="encryptiontype"></param>
        /// <returns>True if successful</returns>
        public static bool ServiceProviderUpdate
        (
            string serviceproviderid,
            string displayname,
            int serviceprovidertypeid,
            string systemname,
            string publickey,
            string webserviceurl,
            string contactname,
            string contactaddress1,
            string contactaddress2,
            string contactcity,
            string contactprovince,
            string contactpostalcode,
            string contactcountry,
            string contactphone,
            string contactfax,
            string contactemail,
            string encryptiontype
        )
        {
            bool result = true;
            string SQL = "ServiceProviderUpdate";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            //if (Convert.ToInt32(db.ExecuteScalar(SQL,                
            //    displayname,
            //    serviceprovidertypeid,
            //    systemname,
            //    publickey,
            //    webserviceurl,
            //    contactname,
            //    contactaddress1,
            //    contactaddress2,
            //    contactcity,
            //    contactprovince,
            //    contactpostalcode,
            //    contactcountry,
            //    contactphone,
            //    contactfax,
            //    contactemail,
            //    encryptiontype)) <= 0)
            //    result = false;            

            if (db.ExecuteNonQuery(SQL,
                serviceproviderid,
                displayname,
                serviceprovidertypeid,
                systemname,
                publickey,
                webserviceurl,
                contactname,
                contactaddress1,
                contactaddress2,
                contactcity,
                contactprovince,
                contactpostalcode,
                contactcountry,
                contactphone,
                contactfax,
                contactemail,
                encryptiontype) <= 0)
                result = false;

            return result;
        }

        #endregion
    }
}
